-- EXEC SP_LOAD_FG_PARTS 'SIT9102AN-243N25E200.00000', '300MHz,7.0x5.0mm,3.3V,50ppm,LVDS oscillator'


ALTER PROC SP_LOAD_FG_PARTS @Part_Number VARCHAR(50), @Description VARCHAR(100), @Pkg_Type VARCHAR(50), @Product_Family VARCHAR(50)
AS
BEGIN
	DECLARE @BASE_PART VARCHAR(50)
	DECLARE @PACKAGE_TYPE VARCHAR(2)
	DECLARE @ASSEMBLY_PART_SUBSTRING VARCHAR(50)
	DECLARE @ASSEMBLY_PROCESSSTEP_ID INT
	DECLARE @PRETEST_PROCESSSTEP_ID INT
	DECLARE @PARENT_PRODUCT_ID INT
	DECLARE @ASSEMBLY_PART_NUMBER VARCHAR(50)
	DECLARE @PRETEST_PART_NUMBER VARCHAR(50)
	DECLARE @ROW_COUNT INT
	DECLARE @FT_PROCESSSTEP_ID INT
	DECLARE @TR_PROCESSSTEP_ID INT
	DECLARE @FG_PROCESSSTEP_ID INT
	DECLARE @PART_NO_END_CHAR VARCHAR(2)
	DECLARE @error_string 		VARCHAR(500)

	IF ( LEFT(@Part_Number, 3) = 'SIT' AND CHARINDEX('FP', @Part_Number ) = 0 AND CHARINDEX('TU', @Part_Number ) = 0 AND CHARINDEX('TX', @Part_Number ) = 0 AND CHARINDEX('TL', @Part_Number ) = 0  )
	BEGIN
		SET @ASSEMBLY_PART_SUBSTRING = SUBSTRING( @Part_Number, 4, 4 )

		IF @ASSEMBLY_PART_SUBSTRING = '9001'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '8102'
		END
		ELSE IF @ASSEMBLY_PART_SUBSTRING = '3700'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '8102'
		END
		ELSE IF @ASSEMBLY_PART_SUBSTRING = '8103'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '8003'
		END
		ELSE IF @ASSEMBLY_PART_SUBSTRING = '9003'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '8003'
		END

		ELSE IF @ASSEMBLY_PART_SUBSTRING = '1601'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '8003'
		END
		ELSE IF @ASSEMBLY_PART_SUBSTRING = '8503'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '8003'
		END
		ELSE IF @ASSEMBLY_PART_SUBSTRING = '9107'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '9102'
		END

		ELSE IF @ASSEMBLY_PART_SUBSTRING = '8004'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '8004'
		END

		ELSE IF @ASSEMBLY_PART_SUBSTRING = '3701'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '8003'
		END
		ELSE IF @ASSEMBLY_PART_SUBSTRING = '9002'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '9102'
		END
		ELSE IF @ASSEMBLY_PART_SUBSTRING = '3702'
		BEGIN
			SET @ASSEMBLY_PART_SUBSTRING = '9102'
		END

		IF @ASSEMBLY_PART_SUBSTRING = '9102'
		BEGIN
			SET @PACKAGE_TYPE = SUBSTRING( @Part_Number, CHARINDEX ( '-', @Part_Number  )+2 , 1 )
		END
		ELSE
		BEGIN
			SET @PACKAGE_TYPE = SUBSTRING( @Part_Number, CHARINDEX ( '-', @Part_Number  )+1 , 1 )
		END

		SELECT @ROW_COUNT = COUNT(*) FROM PRODUCT WHERE PART_NUMBER  = @Part_Number
		IF( @ROW_COUNT < 1 )
		BEGIN

			SELECT @PRETEST_PROCESSSTEP_ID = P.PROCESSSTEP_ID, @PRETEST_PART_NUMBER = P.pART_NUMBER, @PARENT_PRODUCT_ID = P.P_PARENT_PRODUCT_ID FROM PRODUCT P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND P.PART_NUMBER LIKE '%'+ @ASSEMBLY_PART_SUBSTRING + '-' + @PACKAGE_TYPE + '%' AND PS.PS_Type = 'Pre Test' 

			IF @PRETEST_PROCESSSTEP_ID IS NOT NULL AND @PRETEST_PROCESSSTEP_ID > 0
			BEGIN
				--PRINT 'Inserting new part number'
				--INSERT INTO Table1 VALUES( @Part_Number, @PACKAGE_TYPE, @ASSEMBLY_PART_SUBSTRING, 'New Product Created' )
				
				--CREATE FT PART NUMBER
				-- INSERT INTO PROCESS STEP TABLE
				INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FT', 'TEST', @Description, CURRENT_TIMESTAMP)
				SELECT @FT_PROCESSSTEP_ID = @@IDENTITY
				-- INSERT INTO PRODUCT TABLE
				INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
					VALUES(@Part_Number, @FT_PROCESSSTEP_ID, @PARENT_PRODUCT_ID, @Description, CURRENT_TIMESTAMP, 0, 'PROD', 0 )
				-- INSERT INTO PROCESS STEP LOCATION TABLE
				INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
					SELECT @FT_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'TEST'
				-- INSERT PROCESS PARAMETERS
				INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
					SELECT @FT_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
					DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FT'

				--PRINT 'FT ProcessStep_Id ' + CONVERT( VARCHAR, @FT_PROCESSSTEP_ID )
				-- INSERT INTO PRODUCT TRAVERSE TABLE
				INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @PRETEST_PROCESSSTEP_ID, @FT_PROCESSSTEP_ID, NULL )

				SET @PART_NO_END_CHAR = substring(@Part_Number, len(@Part_Number), len(@Part_Number) )

				IF @PART_NO_END_CHAR = 'Y' OR @PART_NO_END_CHAR = 'T'
				BEGIN
					--CREATE TAPE & REEL PART NUMBER WHEN PART ENDS WITH 'T' OR 'Y'
					-- INSERT INTO PROCESS STEP TABLE
					INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'Tape & Reel', 'Class Store', @Description, CURRENT_TIMESTAMP)
					SELECT @TR_PROCESSSTEP_ID = @@IDENTITY
					-- INSERT INTO PRODUCT TABLE
					INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID, P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
						VALUES(@Part_Number, @TR_PROCESSSTEP_ID, @PARENT_PRODUCT_ID, @Description, CURRENT_TIMESTAMP, 0, 'PROD', 0 )
					-- INSERT INTO PROCESS STEP LOCATION TABLE
					INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
						SELECT @TR_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Class Store'
						-- INSERT PROCESS PARAMETERS
					INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
						SELECT @TR_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
						DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'Tape & Reel'

						--PRINT 'Tape & Reel ProcessStep_Id ' + CONVERT( VARCHAR, @TR_PROCESSSTEP_ID )
						-- INSERT INTO PRODUCT TRAVERSE TABLE
					INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FT_PROCESSSTEP_ID, @TR_PROCESSSTEP_ID, NULL )
				END		
				--CREATE FG PART NUMBER 
				INSERT INTO Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )VALUES( 'FG', 'Finished Goods', @Description, CURRENT_TIMESTAMP)
				SELECT @FG_PROCESSSTEP_ID = @@IDENTITY
				-- INSERT INTO PRODUCT TABLE
				INSERT INTO Product( Part_Number, ProcessStep_ID, P_Parent_Product_ID,P_Marketing_Part_Number,P_Comments,TimeStamp,Is_Approved, PART_STATUS, IS_PRIMARY_FG )
					VALUES(@Part_Number, @FG_PROCESSSTEP_ID, @PARENT_PRODUCT_ID, @Part_Number, @Description, CURRENT_TIMESTAMP, 0, 'PROD', 0 )
				-- INSERT INTO PROCESS STEP LOCATION TABLE
				INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
					SELECT @FG_PROCESSSTEP_ID, LOCATION_ID, CURRENT_TIMESTAMP FROM LOCATION WHERE L_TYPE = 'Finished Goods'
				-- INSERT PROCESS PARAMETERS
				INSERT INTO Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)
					SELECT @FG_PROCESSSTEP_ID,DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_Value FROM Default_Process_Parameter_Table DPPT, Process_Types_Table PTT WHERE
					DPPT.ProcessTypeID = PTT.ProcessTypeID AND PTT.Process_Type = 'FG'
				
				--PRINT 'FG ProcessStep_Id ' + CONVERT( VARCHAR, @FG_PROCESSSTEP_ID )
				-- INSERT INTO PRODUCT TRAVERSE TABLE
				IF @PART_NO_END_CHAR = 'Y' OR @PART_NO_END_CHAR = 'T'
				BEGIN
					INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @TR_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, NULL )
				END
				ELSE
				BEGIN
					INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @FT_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, NULL )
				END
				
				IF @ASSEMBLY_PART_SUBSTRING = '8003'
				BEGIN
					SELECT @PRETEST_PROCESSSTEP_ID = P.PROCESSSTEP_ID, @PRETEST_PART_NUMBER = P.pART_NUMBER, @PARENT_PRODUCT_ID = P.P_PARENT_PRODUCT_ID FROM PRODUCT P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND P.PART_NUMBER LIKE '%'+ @ASSEMBLY_PART_SUBSTRING + 'B-' + @PACKAGE_TYPE + '%' AND PS.PS_Type = 'Pre Test' 
					IF @PRETEST_PROCESSSTEP_ID IS NOT NULL AND @PRETEST_PROCESSSTEP_ID > 0
					BEGIN
						INSERT INTO Process_Step_Traverse_Table (PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID )VALUES( @PRETEST_PROCESSSTEP_ID, @FT_PROCESSSTEP_ID, NULL )
					END
				END
				SET @error_string = @Part_Number + ' Successfully Updated IN CHIPTRAC SYSTEM : '
				RAISERROR( @error_string, 16, 1 ) WITH LOG
				RETURN

			END
			ELSE
			BEGIN
				--INSERT INTO TABLE1 (Part_number, Reason ) VALUES( @Part_Number, 'Assembly Part Number not Found ' )
				--Print 'Assembly Part Number not Found ' + @Part_Number
				SET @error_string = 'Pre Test PART NUMBER not Found IN CHIPTRAC SYSTEM : "' + @Part_Number + '"'
				RAISERROR( @error_string, 16, 1 ) WITH LOG
				RETURN
			END
		END
		ELSE
		BEGIN
			--PRINT 'Part Number already exists'
			SET @error_string = 'PART NUMBER ALREADY EXISTS IN CHIPTRAC SYSTEM: "' + @Part_Number + '"'
			RAISERROR( @error_string, 16, 1 ) WITH LOG
			RETURN
		END
	END
	ELSE
	BEGIN
		--INSERT INTO TABLE1 (Part_number, Reason ) VALUES( @Part_Number, 'NOT A STANDARD PART NUMBER' )
		--print 'NOT A STANDARD PART NUMBER ' + @Part_Number
		IF CHARINDEX('TU', @Part_Number ) > 0 OR CHARINDEX('TX', @Part_Number ) > 0 OR CHARINDEX('FP', @Part_Number ) > 0 OR CHARINDEX('TL', @Part_Number ) > 0
		BEGIN
			SET @error_string = 'PROGRAMMABLE PART NUMBER : "' + @Part_Number + '"'
		END
		ELSE
		BEGIN
			SET @error_string = 'NOT A STANDARD PART NUMBER : "' + @Part_Number + '"'
		END
		--SET @error_string = 'NOT A STANDARD PART NUMBER : "' + @Part_Number + '"'
		RAISERROR( @error_string, 16, 1 ) WITH LOG
		RETURN
	END
END